﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Configuration;
using Runda.CMS.Order.Entity;
using Runda.CMS.Common.Utility;
using System.Linq.Expressions;
using System.Data.Linq.Mapping;
using System.Data.Linq;
using System.Data;
using System.Data.Common;
using System.Data.Sql;
using System.Data.SqlClient;

namespace Runda.CMS.Order.Repository
{
    public class ShippingRequestRepository
    {
        private string db = ConfigurationManager.ConnectionStrings["OrderConnectionString"].ConnectionString;

        public Object findShippingRequestLists(string statusName,String customerName, string inventoryName, string billNum, string sendNum, String orderNum, int pageSize, int pageIndex, out int recordCount) {
            string sql = @"select * from (select d.ReceivedDate,d.cSendbillNo,d.ReceivedMan, d.preReceivedDate, a.cVouchId,a.cCusCode,d.cSendcompany,
 a.cInvBarCode,a.cOrderId,a.cCustomerName as cCusName,a.cInvName,a.cInvBarCode AS cInvCode,a.cInvStd as inventoryStandard,
 a.cInvM_Unit,case when d.ReceivedDate is not null then '已签收' else case when d.cSendcompany is not null then '快递中' else a.cStatus end end as cStatus,a.cBatchCode,a.cVDate,a.cDate,isnull(cast(a.iQuantity as decimal(18,2)),0.00) as sendQuantity,isnull(cast(a.iSQuantity as decimal(18,2)),0.00) as sendIsQuantity from Out_RdRecord a join
  (select distinct cOrderID from Out_Orders) as e on a.cOrderID = e.cOrderID  left join (select cVouchId,
   cSendbillNo,ReceivedMan,cSendcompany,convert(varchar(17),preReceivedDate,121) as preReceivedDate,convert(varchar(17),ReceivedDate,121) as   ReceivedDate 
   from VouchSend group by cVouchId,cSendbillNo,ReceivedMan,cSendcompany,preReceivedDate,ReceivedDate) as d on 
   d.cVouchId = a.cVouchId) as a where 1=1";

            if (customerName != "")
            {
                sql += " and a.cCusName like'%" + customerName + "%'";
            }
            if (inventoryName != "")
            {
                sql += " and a.cInvName like '%" + inventoryName + "%'";
            }
            if (orderNum != "")
            {
                sql += " and a.cOrderId='" + orderNum + "'";
            }
            if (billNum != "") {
                sql += " and a.cVouchId = '" + billNum + "'";
            }
            if (sendNum != "") {
                sql += " and a.cSendbillNo = '" + sendNum + "'";
            }
            if (statusName != "--不限--")
            {
                sql += " and a.cStatus = '" + statusName + "'";
            }
            OrderDataContext context = new OrderDataContext(db);
            context.Connection.Open();
            SqlCommand command = new SqlCommand(sql, (SqlConnection)context.Connection);
            List<OrderInfo> list = new List<OrderInfo>();
            using (DbDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection))
            {
                list = context.Translate<OrderInfo>(reader).Skip((pageIndex - 1) * pageSize).Take(pageSize).ToList();
            }
            context.Connection.Open();
            using (DbDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection))
            {
                recordCount = context.Translate<OrderInfo>(reader).Count();
            }
            return list;         
        }

        public List<ShippingRequest> getShippingRequestByOrderID(String id,String inventoryCode,String customerID) {
            List<ShippingRequestDetail> detailList = null; 
            using (OrderDataContext context = new OrderDataContext(db)) {
                var od = from shippingRequestDetail in context.shippingRequestDetails
                         where shippingRequestDetail.orderID == id &&
                                        (inventoryCode == ""? true:shippingRequestDetail.inventoryId == inventoryCode) &&
                                        (customerID == ""? true: shippingRequestDetail.customerId ==customerID)
                         select shippingRequestDetail;
               detailList = od.ToList<ShippingRequestDetail>();
            }
            List<ShippingRequest> shippingRequests = new List<ShippingRequest>();
            foreach(ShippingRequestDetail detail in detailList){
                bool isExist = false;
                foreach (ShippingRequest sr in shippingRequests) {                   
                    if (detail.requestID == sr.requestId) {
                        sr.shippingDetails.Add(detail);
                        isExist = true;
                    }
                }
                if (!isExist) {
                    ShippingRequest request = new ShippingRequest();
                    request.requestId = detail.requestID;
                    request.shippingDetails.Add(detail);
                    shippingRequests.Add(request);
                }
            }
            return shippingRequests;
        }

        public List<ShippingRequest> getUnShippingRequests(String orderId) {
            List<ShippingRequestDetail> detailList = null; 
            using (OrderDataContext context = new OrderDataContext(db)) { 
                var od = from shippingRequestDetail in context.shippingRequestDetails
                            where 
                                        !(from shipping in context.shipping   
                                            select  shipping.shippingRequestId).Contains(shippingRequestDetail.requestID) &&
                                        shippingRequestDetail.orderID == orderId
                                        select shippingRequestDetail;
                detailList = od.ToList<ShippingRequestDetail>();
            }
            List<ShippingRequest> shippingRequests = new List<ShippingRequest>();
            foreach (ShippingRequestDetail detail in detailList)
            {
                bool isExist = false;
                foreach (ShippingRequest sr in shippingRequests)
                {
                    if (detail.requestID == sr.requestId)
                    {
                        sr.shippingDetails.Add(detail);
                        isExist = true;
                    }
                }
                if (!isExist)
                {
                    ShippingRequest request = new ShippingRequest();
                    request.requestId = detail.requestID;
                    request.shippingDetails.Add(detail);
                    shippingRequests.Add(request);
                }
            }
            return shippingRequests;
        }

        public List<ShippingRequestDetail> getDetailByRequestId(String requestId) {
            using (OrderDataContext context = new OrderDataContext(db))
            {
                var od = from shippingRequestDetail in context.shippingRequestDetails
                         where
                                     shippingRequestDetail.requestID == requestId
                         select shippingRequestDetail;
                return od.ToList<ShippingRequestDetail>();
            }
        }

        public Object getRequestDetailsByRequestId(String requestId) {
            using (OrderDataContext context = new OrderDataContext(db))
            {
                var od = (from shippingRequestDetail in context.shippingRequestDetails
                          
                          where shippingRequestDetail.requestID == requestId
                          select new { 
                                       customerName = shippingRequestDetail.CusName,
                                       inverntoryCode = shippingRequestDetail.inventoryId,
                                       inventoryName = shippingRequestDetail.invName,
                                       inventorystd = shippingRequestDetail.invStd,
                                       shippingRequestId = shippingRequestDetail.requestID,
                                       sendQuantity = shippingRequestDetail.quantity
                          });
                return od.ToList();
            }
        }
    }
}
